

package com.uit.ecommerce.access.mapper;

import com.uit.ecommerce.dto.CityDTO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class CityMapper extends MapperDB{
    public CityMapper() throws Exception{
        super();
    }

    /**
     *
     * @param city
     * @param rs
     * @throws Exception
     */
    private void InitCityDTOFromRs(CityDTO city, ResultSet rs) throws Exception{
        if((rs != null) && (city != null)){
            city.setId(rs.getString("MaTinhTP"));
            city.setName(rs.getString("TinhTP"));
            city.setAreaId(rs.getString("MaKhuVuc"));
        }                    
    }

    /**
     * 
     * @return
     * @throws Exception
     */
    public ArrayList<CityDTO> GetAllCity() throws Exception{
        ArrayList<CityDTO> listResult = new ArrayList<CityDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from ecormerce.tinhtp");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            while((rs != null) && (rs.next())){
                CityDTO cityTemp = new CityDTO();
                InitCityDTOFromRs(cityTemp, rs);
                listResult.add(cityTemp);
            }
        }catch(Exception ex){
            throw ex;
        }

        return listResult;
    }

    /**
     * Get an CityDTO object by it's code
     * @param id code of City
     * @return CityDTO found
     * @throws Exception
     */
    public CityDTO GetCityById(String id) throws Exception{
        CityDTO result = new CityDTO();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from ecormerce.tinhtp ");
            sql.append("where MaTinhTP = '").append(id);
            sql.append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            if((rs != null) && (rs.next())){
                InitCityDTOFromRs(result, rs);
            }
        }catch(Exception ex){
            throw ex;
        }

        return result;
    }

    /**
     * Get an City by it's Name
     * @param Name Name for search
     * @return CityDTO found, null if not found
     * @throws Exception
     */
     public CityDTO GetCityByName(String Name) throws Exception{
        CityDTO result = new CityDTO();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from ecormerce.tinhtp ");
            sql.append("where TinhTP = '").append(Name);
            sql.append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            if((rs != null) && (rs.next())){
                InitCityDTOFromRs(result, rs);
            }
        }catch(Exception ex){
            throw ex;
        }

        return result;
    }

    /**
     *
     * @param Id
     * @return
     * @throws Exception
     */
    public boolean IsIdExisted(String Id) throws Exception{
        boolean result = false;
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from ecormerce.tinhtp ");
            sql.append("where MaTinhTP = '").append(Id);
            sql.append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            if((rs != null) && (rs.next())){
                result = true;
            }
        }catch(Exception ex){
            throw ex;
        }
        return result;
    }

    /**
     * Get list City by area code
     * @param areaId code of Area
     * @return list City in area with code is areaId
     * @throws Exception
     */
    public ArrayList<CityDTO> GetCityByAreaCode(String areaId) throws Exception{
        ArrayList<CityDTO> listResult = new ArrayList<CityDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from ecormerce.tinhtp ");
            sql.append("where MaKhuVuc = '").append(areaId);
            sql.append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            while((rs != null) && (rs.next())){
                CityDTO cityTemp = new CityDTO();
                InitCityDTOFromRs(cityTemp, rs);
                listResult.add(cityTemp);
            }
        }catch(Exception ex){
            throw ex;
        }

        return listResult;
    }
    public ArrayList<CityDTO> GetCityByAreaName(String area) throws Exception{
        ArrayList<CityDTO> listResult = new ArrayList<CityDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select TinhTp, MaTinhTp, TinhTp.MaKhuVuc from ecormerce.khuvuc,ecormerce.tinhtp where khuvuc.MaKhuVuc=tinhtp.MaKhuVuc and KhuVuc='"+area+"'");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            while((rs != null) && (rs.next())){
                CityDTO cityTemp = new CityDTO();
                InitCityDTOFromRs(cityTemp, rs);
                listResult.add(cityTemp);
            }
        }catch(Exception ex){
            throw ex;
        }

        return listResult;
    }

    /**
     * Insert an city into database
     * @param city city to insert
     * @return true if successful, and fale if fail
     * @throws Exception
     */
    public boolean InsertCity(CityDTO city) throws Exception{
        boolean result = false;
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Insert into ecormerce.tinhtp values( ");
            sql.append("'").append(city.getName()).append("', ");
            sql.append("'").append(city.getId()).append("', ");
            sql.append("'").append(city.getAreaId()).append("')");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            result = stmt.execute();
        }catch(Exception ex){
            throw ex;
        }
        return result;
    }

    /**
     * Delete an city in database
     * @param id Id of city need to delete
     * @throws Exception
     */
    public void DeleteCity(String id) throws Exception{
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Delete from ecormerce.tinhtp Where MaTinhTP = '");
            sql.append(id).append("'");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
        }catch(Exception ex){
            throw ex;
        }
    }
}